package com.mp.approvalreq.dao.impl;

import com.mp.approvalreq.dao.SubmitRequestDao;
import com.mp.approvalreq.entity.*;
import com.mp.approvalreq.util.Formatter;
import com.mp.user.entity.User;
import com.plusmoney.util.SQLLogger;
import com.plusmoney.util.Utils;
import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SubmitRequestDaoImpl implements SubmitRequestDao {

    private Logger logger = Logger.getLogger(SubmitRequestDaoImpl.class);

    private Connection conn;

    public SubmitRequestDaoImpl(Connection connection) {
        this.conn = connection;
    }


    @Override
    public Request getRequestRefuseChain(Request request) throws Throwable {
        String sql = "select Frefuse_chain, Ffinished, Frequest_ancestor_id, Fis_resubmit"
                + " from approval.request_flow"
                + " where Frequest_id = ?";


        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            int parentRequestId = Utils.isNull(request.getParentRequestId()) ? 0 : request.getParentRequestId();
            pstm.setInt(1, parentRequestId);
            logger.info(Formatter.aformat(sql, parentRequestId));

            try (ResultSet rs = pstm.executeQuery()) {
                if (rs.next()) {
                    request.setRefuseChain(rs.getString(1));
                    request.setFinished(rs.getInt(2));
                    request.setRequestAncestorId(rs.getInt(3));
                    request.setIsResubmit(rs.getInt(4));
                }
            }
        }

        return request;
    }


    @Override
    public void updateRequestResubmit(int requestId, int companyId) throws Throwable {

        String sql = " update approval.request_flow set Fis_resubmit = 1 " +
                " where Frequest_id = ? and Fcompany_id = ? and Fis_resubmit = 0 ";

        logger.info(Formatter.aformat(sql, requestId, companyId));

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, requestId);
            ps.setInt(2,companyId);

            ps.executeUpdate();
        }
    }


    @Override
    public int getTimesCurrentMonthPersonUsed(int companyInfoId, int templateType) throws Throwable {
        int times = 0;

        String sql = " select count(*) from approval.request_flow where Fapplicant_ciid = ? " +
                " and year(Fcreate_time) = year(NOW()) and month(Fcreate_time) = month(NOW()) and Ffinished in(0,1,2) " +
                " and Frequest_template_type = ? ";

        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            pstm.setInt(1, companyInfoId);
            pstm.setInt(2, templateType);
            logger.info(Formatter.aformat(sql, companyInfoId, templateType));

            try (ResultSet rs = pstm.executeQuery()) {
                if (rs.next()) {
                    times = rs.getInt(1);
                }
            }
            return times;
        }
    }

    @Override
    public void saveExportEmail(Email email, User user) throws Throwable {
		/*
		   这两条SQL语句可以改写成(前提：数据表需要设计Fcompany_id是唯一约束)：
		   	insert into approval.export_approval_email set Fcompany_id = ?,Fmain_contact_id = ?,Femail=?
		   		on duplicate key update Fmain_contact_id=?,Femail=?
		 */
        if (email.getId() == 0) {
            String sql = " insert into approval.export_approval_email set Fcompany_id = ?,"
                    + " Fmain_contact_id = ?, Femail = ?, Fciid = ? ";
            try (PreparedStatement pstm = conn.prepareStatement(sql)) {
                pstm.setInt(1, user.getCompanyId());
                pstm.setInt(2, user.getContactId());
                pstm.setString(3, email.getEmail());
                pstm.setInt(4, user.getCompanyInfoId());
                logger.info(Formatter.aformat(sql, user.getCompanyId(),
                        user.getContactId(), email.getEmail(), user.getCompanyInfoId()));
                pstm.executeUpdate();
            }
        } else {
            String sql = " update approval.export_approval_email set Fmain_contact_id = ?, Fciid = ?, Femail = ? "
                    + " where Fcompany_id = ? and Fid = ?";
            try (PreparedStatement pstm = conn.prepareStatement(sql)) {
                pstm.setInt(1, user.getContactId());
                pstm.setInt(2, user.getCompanyInfoId());
                pstm.setString(3, email.getEmail());
                pstm.setInt(4, user.getCompanyId());
                pstm.setInt(5, email.getId());
                logger.info(Formatter.aformat(sql, user.getContactId(), user.getCompanyInfoId(),
                        email.getEmail(), user.getCompanyId(), email.getId()));
                pstm.executeUpdate();
            }
        }

    }


    @Override
    public Email getExportEmail(User user) throws Throwable {
        String sql = " select Fid,Femail from approval.export_approval_email where Fcompany_id = ? and Fciid = ?";
        Email email = new Email();
        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            pstm.setInt(1, user.getCompanyId());
            pstm.setInt(2, user.getCompanyInfoId());
            logger.info(Formatter.aformat(sql, user.getCompanyId(), user.getCompanyInfoId()));
            try (ResultSet rs = pstm.executeQuery()) {
                if (rs.next()) {
                    email.setId(rs.getInt(1));
                    email.setEmail(rs.getString(2));
                }
            }
        }
        return email;
    }


    @Override
    public boolean isSubmitAgain(Integer companyId, String contactIdSubmitTime) throws SQLException {

        String sql = " select Frequest_id from approval.request_flow " +
                " where Fcompany_id = ? and Fcontact_submit_time = ? ";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setString(2, contactIdSubmitTime);

            logger.info(SQLLogger.logSQL(sql, companyId, contactIdSubmitTime));

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return true;
                }
            }
        }

        return false;
    }


}
